import datetime
import json
import os
import threading
from random import shuffle
import MySQLdb
import math
import pymysql
import datetime


def get_now_time():
    tz = datetime.datetime.now()
    end_time = tz.strftime("%Y-%m-%d %H:%M:%S")
    return end_time


def select_mysql(db_info):
    host = db_info['address']
    user = db_info["user"]
    passwd = db_info["passwd"]
    port = db_info["port"]
    database = db_info["database"]
    try:
        conn = pymysql.connect(
            host=host,
            user=user,
            passwd=passwd,
            port=int(port),
            database=database,
            charset='utf8',
            cursorclass=pymysql.cursors.DictCursor
        )
        cur = conn.cursor()
        sql = db_info["sql"]
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        conn.close()
        return res
    except Exception as e:
        return {"code": "400", "msg": "连接失败 %s:%s " % (host, port)}


def conn_mysql(db_info):
    host = db_info['address']
    user = db_info["user"]
    passwd = db_info["passwd"]
    port = db_info["port"]
    database = db_info["database"]
    try:
        conn = MySQLdb.connect(
            host=host,
            user=user,
            passwd=passwd,
            port=int(port),
            database=database,
            charset='utf8'
        )
        cur = conn.cursor()
        sql = db_info["sql"]
        cur.execute(sql)
        res = cur.fetchall()
        cur.close()
        conn.close()
        return {"code": "200", "msg": res}
    except Exception as e:
        return {"code": "400", "msg": "连接失败 %s:%s " % (host, port)}


def qury_mysql_data(db_info):
    qery_info = {'address': db_info["s_address"], 'passwd': db_info["s_passwd"], 'port': db_info["s_port"],
                 'user': db_info["s_user"], "db_data": db_info["data"], "limit": db_info["limit"]}
    insert_info = {'address': db_info["d_address"], 'passwd': db_info["d_passwd"], 'port': db_info["d_port"],
                   'user': db_info["d_user"]}
    field_list = []
    return_data = []
    field_prop = {}
    for field_info in qery_info["db_data"]:
        fields = field_info[0]
        db_name = fields["dbname"]
        tb_name = fields["tablename"]
        fd_name = fields["fieldname"]
        fd_label = fields["label"]
        if fd_label not in "datetime":
            field_list.append(fields['fieldname'])
        field_prop.update({fd_name: fd_label})
    feild = ','.join(field_list)

    max_count_sql = "select count(*) from " + db_name + "." + tb_name
    q_sql_info = qery_info
    q_sql_info.update({"sql": max_count_sql, "database": db_name})

    max_count_res = conn_mysql(q_sql_info)
    max_count_num = max_count_res["msg"][0][0]

    limit = qery_info["limit"]
    page_nums = max_count_num / int(limit)
    page_num = math.ceil(page_nums)
    if page_num <= 1:
        page = 1
    elif page_num > 1:
        page = page_num
    for i in range(page):
        q_data_sql = "select " + feild + " from " + db_name + "." \
                     + tb_name + " limit " + str(i * int(limit)) + "," + limit
        q_sql_info.update({"sql": q_data_sql})
        data_res = select_mysql(q_sql_info)
        shuffle_data = shuffle_str(data_res, field_prop)
        insert_info.update({'data': shuffle_data, 'dbname': db_name, 'tbname': tb_name})
        res = insert_mysql_data(insert_info)
        return_data.append(res)
    return return_data


def shuffle_str(feild_data, field_prop):
    # {"字段":"值"}
    for to_up_data in feild_data:
        for to_up_data_key in to_up_data.keys():
            if to_up_data_key != "id" and len(str(to_up_data[to_up_data_key])) > 2 \
                    and field_prop[to_up_data_key] != "datetime":
                if field_prop[to_up_data_key] == "json":
                    vaule_lists = to_up_data[to_up_data_key]

                    vaule_lists_dict = json.loads(vaule_lists)
                    for key in vaule_lists_dict.keys():
                        if key != "":
                            try:
                                value_d = vaule_lists_dict[key]
                                value_d = list(value_d)
                                shuffle(value_d)
                                value_d = ''.join(value_list)
                                vaule_lists_dict.update({key: value_d})
                            except TypeError:
                                pass

                    to_up_data[to_up_data_key] = json.dumps(vaule_lists_dict)
                else:
                    value_list = list(to_up_data[to_up_data_key])
                    shuffle(value_list)
                    desen_vaule = ''.join(value_list)
                    to_up_data[to_up_data_key] = desen_vaule
    return feild_data


def insert_mysql_data(insert_datas):
    if insert_datas:
        db_table = insert_datas["dbname"] + "." + insert_datas["tbname"]
        err_data = []
        host = insert_datas["address"]
        port = insert_datas["port"]
        user = insert_datas["user"]
        passwd = insert_datas["passwd"]
        database = insert_datas["dbname"]
        # print(host, port, user, passwd)
        db_conn = pymysql.connect(host=host, port=int(port),
                                  user=user, passwd=passwd,
                                  db=database, charset='utf8', cursorclass=pymysql.cursors.DictCursor)
        cursor = db_conn.cursor()
        insert_num = 0
        for insert_data in insert_datas["data"]:
            insert_num += 1
            row_data = ''
            col_data = ''
            for key in insert_data.keys():
                try:
                    row_data = (row_data + "'%s'" + ",") % (insert_data[key])
                    col_data = (col_data + "%s" + ',') % key
                except:
                    err_data.append(insert_data['id'])
            insert_sql = "insert into %s (%s) values (%s)" % (db_table, col_data[:-1], row_data[:-1])
            # print(insert_sql)
            try:
                cursor.execute(insert_sql)
                db_conn.commit()
            except:
                db_conn.rollback()
        cursor.close()
        db_conn.close()
        # end_time = get_now_time()
        result_data = {"count": insert_num, "失败数据id": err_data}

    return{"count": insert_num, "失败数据id": err_data}


databases_info = {
    's_address': '106.14.10.54', 's_types': 'MySQL', 's_user': 'root', 's_passwd': '123456', 's_port': '33306',
    'd_address': '10.0.172.58', 'd_types': 'MySQL', 'd_user': 'root', 'd_passwd': 'admin123456', 'd_port': '3306',
    'data': [[{'label': 'bigint(20)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'id'}],
             [{'label': 'varchar(45)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'uuid'}],
            [{'label': 'int(11)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'info_id'}],
             [{'label': 'varchar(128)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'info_name'}],
             [{'label': 'int(11)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'status'}],
             [{'label': 'json', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'data'}],
             [{'label': 'datetime', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'created_at'}],
             [{'label': 'datetime', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'updated_at'}],
             [{'label': 'datetime', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'deleted_at'}]],
    'limit': '1000', 'task': '2022422f19591c'}

# [{'label': 'varchar(256)', 'dbname': 'infra_cmdb_dev', 'tablename': 'cmdb_resource_data', 'fieldname': 'data'}],
# qury_mysql_data(databases_info)


from threading import Thread
from multiprocessing.dummy import Pool
from multiprocessing import Process


class MyThread(Thread):
    def run(self) -> None:
        self.task()

    def task(self):

        return qury_mysql_data(self._kwargs)


class MyProcess(Process):
    def run(self) -> None:
        self.task()

    def task(self):
        print(os.getpid(),os.getppid())
        return qury_mysql_data(self._kwargs)


if __name__ == '__main__':
    db_ww = {
        's_address': '10.0.172.58', 's_types': 'MySQL', 's_user': 'root', 's_passwd': 'admin123456', 's_port': '3306',
        'd_address': '106.14.10.54', 'd_types': 'MySQL', 'd_user': 'root', 'd_passwd': '123456', 'd_port': '33306',
        'data': [[{'label': 'bigint(20)', 'dbname': 'thdash', 'tablename': 'policy', 'fieldname': 'id'}],
                 [{'label': 'varchar(128)', 'dbname': 'thdash', 'tablename': 'policy', 'fieldname': 'day'}],
                 [{'label': 'varchar(50)', 'dbname': 'thdash', 'tablename': 'policy', 'fieldname': 'demand_side'}],
                 [{'label': 'varchar(256)', 'dbname': 'thdash', 'tablename': 'policy', 'fieldname': 'demand'}],
                 [{'label': 'varchar(12)', 'dbname': 'thdash', 'tablename': 'policy', 'fieldname': 'operation'}]],
        'limit': '1000', 'task': '2022422dc31e6e'}
    start_time = get_now_time()

    # t1 = MyThread(kwargs=databases_info)
    # t1.start()
    # t1.join()
    p = MyThread(kwargs=db_ww)
    # p.daemon = True
    p.start()
    p.join()
    res = p.task()
    print(res)
    end_time = get_now_time()
    print(start_time, end_time)
